package gmodel

import (
	"fmt"
	"strings"

	"gitee.com/851537/gbase"
	"gitee.com/851537/gmodel/corm"
	"github.com/pkg/errors"
	"gorm.io/gorm"
)

type BaseDb struct {
	gbase.Base

	//数据库连接名
	clientName string `gorm:"-"`

	//主表别名
	alias string `gorm:"-"`
}

func (this *BaseDb) SetClientName(name string) *BaseDb {
	this.clientName = name
	return this
}

func (this *BaseDb) GetClientName() string {
	return this.clientName
}

// 默认返回key=default的crom实例
//func (this *BaseDb) SetClient(c ...*corm.Client) {
//	if len(c) > 0 {
//		this.c = c[0]
//	} else {
//		this.c = corm.ClientIns()
//	}
//}

//这个没用，删除
//func (this *BaseDb) Client(name ...string) *corm.Client { //默认返回主数据库
//	return corm.ClientIns(name...)
//	//return this.c
//}

// 设置主表别名
func (this *BaseDb) SetAlias(alias string) *BaseDb {
	this.alias = alias
	return this
}

func (this *BaseDb) Db() *gorm.DB {
	//return this.Client().Db
	if this.clientName == "" {
		this.clientName = corm.DefaultClientName
	}
	return corm.ClientIns(this.clientName).Db
}

func (this *BaseDb) IsHash() bool {
	return false
}

func (this *BaseDb) Alias(child IDb, key ...string) *gorm.DB {
	alias := "t" //default table alias is t
	if len(key) > 0 {
		alias = key[0]
	} else if this.alias != "" {
		alias = this.alias
	}
	return child.Db().Table(fmt.Sprintf("%s %s", child.TableName(), alias))
}

func (this *BaseDb) Tx(child IDb, fn func(tx *gorm.DB) error) error {
	return this.Db().Transaction(fn)
}

// child必须为指针类型，下同
func (this *BaseDb) DbGet(child IDb, wheres ...any) (err error) {
	//如果有id,tx.First默认会作为条件，所以下面这个不需要
	//if len(wheres) == 0 {
	//	wheres = make([]any, 0)
	//	wheres = append(wheres, "id", child.GetId())
	//}
	tx := this.Alias(child)
	this.buildWheres(tx, wheres...)
	return tx.First(child).Error
}

func (this *BaseDb) DbFirst(child IDb, orderBy string, wheres ...any) (err error) {
	tx := this.Alias(child).Order(orderBy)
	this.buildWheres(tx, wheres...)
	return tx.First(child).Error //first如果没有找到会返回错误，take不会
}

// 对DbPageCol的特例，直接返回[]int64
func (this *BaseDb) DbPageIds(child IDb, idKey, join, order string, page, limit int, wheres ...any) (ids []int64, err error) {
	err = this.DbPageCol(child, &ids, idKey, join, order, page, limit, wheres...)
	return
}

// 获取某列数据并放入list,list必须为指针,比如id列，type列等
func (this *BaseDb) DbPageCol(child IDb, list any, idKey, join, order string, page, limit int, wheres ...any) (err error) {
	if page == 0 {
		page = 1
	}
	if limit == 0 {
		limit = 15
	}
	offset := (page - 1) * limit
	tx := this.Alias(child).Select(idKey).Joins(join).Order(order).Limit(limit).Offset(offset)
	this.buildWheres(tx, wheres...)
	if err = tx.Find(list).Error; err != nil {
		return
	}
	return
}

// copy from DbPageV2
// usage:
//
//	type Item struct {
//			AreaName   string `json:"area_name"`
//			MachineNum int64  `json:"machine_num"`
//		}
func (this *BaseDb) DbPageColsWithOpts(child IDb, list any, page, limit int, opts ...gbase.OptionType[*gorm.DB]) (err error) {
	if page == 0 {
		page = 1
	}
	if limit == 0 {
		limit = 15
	}
	offset := (page - 1) * limit
	tx := this.Alias(child).Limit(limit).Offset(offset)
	for _, opt := range opts {
		opt.Apply(tx)
	}
	if err = tx.Find(list).Error; err != nil {
		return
	}
	return
}

// list示例：*[]*User,*[]map[string]any
func (this *BaseDb) DbPage(child IDb, list any, keys, join, order string, page, limit int, wheres ...any) (count int64, err error) {
	if page == 0 {
		page = 1
	}
	if limit == 0 {
		limit = 15
	}
	offset := (page - 1) * limit
	tx := this.Alias(child).Select(keys).Joins(join).Order(order).Limit(limit).Offset(offset)
	this.buildWheres(tx, wheres...)
	if err = tx.Find(list).Error; err != nil {
		return
	}
	if !(page == 1 && limit == -1) {
		if count, err = this.DbCount(child, join, wheres...); err != nil {
			return
		}
	}
	return
}

func (this *BaseDb) DbPageWithOpts(child IDb, list any, page, limit int, opts ...gbase.OptionType[*gorm.DB]) (count int64, err error) {
	return this.DbPageV2(child, list, page, limit, opts...)
}

// deprecated
// instead by DbPageWithOpts
// 改进使用options方式，
// todo 改进使用无偏移分页代替OFFSET加快速度，即不指定page,以id>xx为条件来获取下一页的数据，首页不用指定该条件
// todo 使用缓存优化计数。
// todo 利用覆盖索引和延迟关联【gorm.scope】来优化查询。
func (this *BaseDb) DbPageV2(child IDb, list any, page, limit int, opts ...gbase.OptionType[*gorm.DB]) (count int64, err error) {
	if page == 0 {
		page = 1
	}
	if limit == 0 {
		limit = 15
	}
	offset := (page - 1) * limit
	tx := this.Alias(child).Limit(limit).Offset(offset)
	for _, opt := range opts {
		opt.Apply(tx)
	}
	if err = tx.Find(list).Error; err != nil {
		return
	}
	if !(page == 1 && limit == -1) {
		if count, err = this.DbCountV2(child, opts...); err != nil {
			return
		}
	}
	return
}

// 获取单表全部字段数据
func (this *BaseDb) DbPageAK(child IDb, list any, join, order string, page, limit int, wheres ...any) (count int64, err error) {
	return this.DbPage(child, list, "t.*", join, order, page, limit, wheres...)
}

func (this *BaseDb) DbAdd(child IDb) (err error) {
	return child.Db().Create(child).Error
}

func (this *BaseDb) DbUpdate(child IDb, keys ...string) (err error) {
	//if len(keys) > 0 {
	//	return this.Client().Db.Model(child).Select(keys).Updates(child).Error
	//} else {
	//	return this.Client().Db.Model(child).Updates(child).Error
	//}
	return errors.New("未实现")
}

func (this *BaseDb) DbSave(child IDb) (err error) {
	return child.Db().Save(child).Error
}

func (this *BaseDb) DbDel(child IDb) (err error) {
	return child.Db().Delete(child).Error
}

func (this *BaseDb) DbCount(child IDb, join string, wheres ...any) (count int64, err error) {
	tx := this.Alias(child).Model(child)
	this.buildWheres(tx, wheres...)
	//for i := 0; i < len(wheres); i += 2 {
	//	db.Where(wheres[i], wheres[i+1])
	//}
	if err = tx.Joins(join).Count(&count).Error; err != nil {
		return 0, err
	}
	return
}

func (this *BaseDb) DbCountWithOpts(child IDb, opts ...gbase.OptionType[*gorm.DB]) (count int64, err error) {
	return this.DbCountV2(child, opts...)
}

// deprecated
// instead by DbCountWithOpts
func (this *BaseDb) DbCountV2(child IDb, opts ...gbase.OptionType[*gorm.DB]) (count int64, err error) {
	tx := this.Alias(child).Model(child)
	for _, opt := range opts {
		if gbase.InArray(opt.Type(), []string{OptionTypeWithDbOrder, OptionTypeWithDbGroup}) {
			continue
		}
		opt.Apply(tx)
	}
	//tx = tx.Order("")
	// 确保使用正确的 COUNT 语法，不要使用 t.*
	// 重置 Select 语句，让 GORM 自动生成正确的 COUNT 查询
	tx = tx.Select("") // 清除之前的 Select
	if err = tx.Count(&count).Error; err != nil {
		return 0, err
	}
	return
}

func (this *BaseDb) DbExist(child IDb, wheres ...any) bool {
	count, _ := this.DbCount(child, "", wheres...)
	return count > 0
}

// 单个字段求和 usage: xx.DbSum(user,"sum(age) as age","left join user_role on user.id=user_role.user_id",wheres...))
func (this *BaseDb) DbSum(child IDb, sumField string, join string, wheres ...any) (num float64, err error) {
	tx := this.Alias(child).Model(child)
	this.buildWheres(tx, wheres...)
	sumField = fmt.Sprintf("COALESCE(%s, 0) as total", sumField)
	err = tx.Joins(join).Select(sumField).Row().Scan(&num)
	return
}

// 多个字段求和 usage:
//
//	type Result struct {
//		Age int64 `json:"age"`
//	}
//
// xx.DbSums(user,&Result,"sum(age) as age","left join user_role on user.id=user_role.user_id",wheres...))
func (this *BaseDb) DbSums(child IDb, result any, sumFields string, join string, wheres ...any) (err error) {
	tx := this.Alias(child).Model(child)
	this.buildWheres(tx, wheres...)
	err = tx.Joins(join).Select(sumFields).Take(result).Error
	return
	//return errors.New("un-impl")
}

func (this *BaseDb) DbSumsWithOpts(child IDb, result any, opts ...gbase.OptionType[*gorm.DB]) (err error) {
	tx := this.Alias(child).Model(child)
	for _, opt := range opts {
		opt.Apply(tx)
	}
	err = tx.Take(result).Error
	return
}

// 支持k,v键值对条件
// 支持最后一个where为raw sql
// 支持map[string]any作为条件
func (this *BaseDb) buildWheres(tx *gorm.DB, wheres ...any) *BaseDb {
	buildWheres(tx, wheres...)
	return this
}

func checkKeysAggFun(keys string) bool {
	lowKeys := strings.ToLower(keys)
	return strings.Contains(lowKeys, "sum(") || strings.Contains(lowKeys, "count(")
}

func WithDbKeys(keys string) gbase.OptionType[*gorm.DB] {
	return gbase.NewOptionType(func(tx *gorm.DB) {
		//TODO 对keys里包含sum,count计算的字段要加上 IFNULL(%s, 0)
		// 处理包含聚合函数的字段，添加IFNULL防止NULL值问题
		if checkKeysAggFun(keys) {
			fields := strings.Split(keys, ",")
			for i, field := range fields {
				field = strings.TrimSpace(field)
				// 如果字段包含聚合函数且不包含NULL处理函数
				if checkKeysAggFun(field) &&
					!strings.Contains(strings.ToUpper(field), "IFNULL") &&
					!strings.Contains(strings.ToUpper(field), "COALESCE") &&
					!strings.Contains(strings.ToUpper(field), "CASE WHEN") {
					// 提取字段别名
					parts := strings.Split(field, " as ")
					alias := ""
					if len(parts) > 1 {
						alias = fmt.Sprintf(" as %s", strings.TrimSpace(parts[len(parts)-1]))
					} else {
						// 如果没有显式别名，使用函数表达式作为别名
						alias = ""
						//alias = strings.TrimSpace(field)
					}
					// 添加IFNULL处理
					fields[i] = fmt.Sprintf("IFNULL(%s, 0) %s", parts[0], alias)
				}
			}
			keys = strings.Join(fields, ", ")
		}

		tx.Select(keys)
	}, "WithDbKeys")
	//return func(tx *gorm.DB) {
	//
	//	//TODO 对keys里包含sum,count计算的字段要加上 IFNULL(%s, 0)
	//	// 处理包含聚合函数的字段，添加IFNULL防止NULL值问题
	//	if checkKeysAggFun(keys) {
	//		fields := strings.Split(keys, ",")
	//		for i, field := range fields {
	//			field = strings.TrimSpace(field)
	//			// 如果字段包含聚合函数且不包含NULL处理函数
	//			if checkKeysAggFun(field) &&
	//				!strings.Contains(strings.ToUpper(field), "IFNULL") &&
	//				!strings.Contains(strings.ToUpper(field), "COALESCE") &&
	//				!strings.Contains(strings.ToUpper(field), "CASE WHEN") {
	//				// 提取字段别名
	//				parts := strings.Split(field, " as ")
	//				alias := ""
	//				if len(parts) > 1 {
	//					alias = fmt.Sprintf(" as %s", strings.TrimSpace(parts[len(parts)-1]))
	//				} else {
	//					// 如果没有显式别名，使用函数表达式作为别名
	//					alias = ""
	//					//alias = strings.TrimSpace(field)
	//				}
	//				// 添加IFNULL处理
	//				fields[i] = fmt.Sprintf("IFNULL(%s, 0) %s", parts[0], alias)
	//			}
	//		}
	//		keys = strings.Join(fields, ", ")
	//	}
	//
	//	tx.Select(keys)
	//}
}

func WithDbWheres(wheres []any) gbase.OptionType[*gorm.DB] {
	return gbase.NewOptionType(func(tx *gorm.DB) {
		buildWheres(tx, wheres...)
	}, "WithDbWheres")
	//return func(tx *gorm.DB) {
	//	buildWheres(tx, wheres...)
	//}
}

func WithDbJoin(join string) gbase.OptionType[*gorm.DB] {
	return gbase.NewOptionType(func(tx *gorm.DB) {
		tx.Joins(join)
	}, "WithDbJoin")
	//return func(tx *gorm.DB) {
	//	tx.Joins(join)
	//}
}

func WithDbOrder(order string) gbase.OptionType[*gorm.DB] {
	return gbase.NewOptionType(func(obj *gorm.DB) {
		obj.Order(order)
	}, OptionTypeWithDbOrder)
	//return func(obj *gorm.DB) {
	//	obj.Order(order)
	//}
}

func WithDbGroup(group string) gbase.OptionType[*gorm.DB] {
	return gbase.NewOptionType(func(obj *gorm.DB) {
		obj.Group(group)
	}, OptionTypeWithDbGroup)
	//return func(obj *gorm.DB) {
	//	obj.Group(group)
	//}
}

func WithDbHaving(having string) gbase.OptionType[*gorm.DB] {
	return gbase.NewOptionType(func(obj *gorm.DB) {
		obj.Having(having)
	}, "WithDbHaving")
	//return func(obj *gorm.DB) {
	//	obj.Having(having)
	//}
}

// 支持k,v键值对条件
// 支持最后一个where为raw sql
// 支持map[string]any作为条件
func buildWheres(tx *gorm.DB, wheres ...any) {
	for i := 0; i < len(wheres); i++ {
		switch v := wheres[i].(type) {
		case string:
			if i+1 < len(wheres) {
				count := strings.Count(v, "?")
				if count == 0 {
					//hlog.Debug(v, wheres[i+1])
					tx.Where(v, wheres[i+1]) // raw SQL case
					i++
				} else {
					// 检查参数是否足够
					//if i+count >= len(wheres) {
					//	panic("insufficient parameters for placeholders in where condition")
					//}
					// 提取参数并传递
					//hlog.Debug(v, wheres[i+1:i+count+1])
					tx.Where(v, wheres[i+1:i+1+count]...)
					i += count // 跳过当前字符串和占位符对应的参数
				}

				//i++
				//count := strings.Count(v, "?")
				//if count == 0 || count == 1 {
				//	//hlog.Debug(v, wheres[i])
				//	tx.Where(v, wheres[i])
				//} else {
				//	//hlog.Debug(v, wheres[i:i+count])
				//	tx.Where(v, wheres[i:i+count]...)
				//	i += count - 1
				//}
			} else {
				tx.Where(v) // raw SQL case
			}
		case map[string]any:
			tx.Where(v)
		// Add more cases as needed for more complex conditions.
		default:
			// Handle invalid cases
			panic(fmt.Sprintf("invalid where condition: %v", v))
		}
	}
	return
}
